{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>62</th>\n",
       "      <th>64</th>\n",
       "      <th>82</th>\n",
       "      <th>107</th>\n",
       "      <th>108</th>\n",
       "      <th>109</th>\n",
       "      <th>110</th>\n",
       "      <th>111</th>\n",
       "      <th>111A</th>\n",
       "      <th>111B</th>\n",
       "      <th>112</th>\n",
       "      <th>112A</th>\n",
       "      <th>112B</th>\n",
       "      <th>113</th>\n",
       "      <th>114</th>\n",
       "      <th>115</th>\n",
       "      <th>117</th>\n",
       "      <th>123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.053</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.078</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.081</td>\n",
       "      <td>0.104</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.075</td>\n",
       "      <td></td>\n",
       "      <td>0.19</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.301</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.061</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.071</td>\n",
       "      <td>0.082</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.087</td>\n",
       "      <td>0.135</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.695</td>\n",
       "      <td>0.129</td>\n",
       "      <td></td>\n",
       "      <td>0.045</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>0.71</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.667</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.281</td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.208</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.172</td>\n",
       "      <td>0.231</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.216</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.186</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.201</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td></td>\n",
       "      <td>0.25</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.047</td>\n",
       "      <td>0.055</td>\n",
       "      <td>0.036</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.15</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.305</td>\n",
       "      <td></td>\n",
       "      <td>0.035</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>L</th>\n",
       "      <td>0.29</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.089</td>\n",
       "      <td>0.077</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.041</td>\n",
       "      <td></td>\n",
       "      <td>0.07</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.333</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.105</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.089</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.043</td>\n",
       "      <td></td>\n",
       "      <td>0.056</td>\n",
       "      <td></td>\n",
       "      <td>0.126</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.18</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.043</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.068</td>\n",
       "      <td>0.058</td>\n",
       "      <td></td>\n",
       "      <td>0.046</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.096</td>\n",
       "      <td>0.066</td>\n",
       "      <td>0.107</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.224</td>\n",
       "      <td>0.202</td>\n",
       "      <td>0.116</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.169</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td></td>\n",
       "      <td>0.75</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.041</td>\n",
       "      <td>0.063</td>\n",
       "      <td>0.064</td>\n",
       "      <td>0.065</td>\n",
       "      <td>0.109</td>\n",
       "      <td>0.059</td>\n",
       "      <td></td>\n",
       "      <td>0.131</td>\n",
       "      <td>0.111</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.092</td>\n",
       "      <td>0.104</td>\n",
       "      <td>0.073</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.085</td>\n",
       "      <td>0.1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.075</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.348</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.174</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.139</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.154</td>\n",
       "      <td>0.143</td>\n",
       "      <td>0.118</td>\n",
       "      <td>0.542</td>\n",
       "      <td>0.508</td>\n",
       "      <td>0.243</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.435</td>\n",
       "      <td></td>\n",
       "      <td>0.322</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      62    64     82    107    108    109    110    111   111A   111B    112  \\\n",
       "aa                                                                              \n",
       "A                        0.1  0.053  0.046  0.078  0.113  0.081  0.104          \n",
       "C                                                                               \n",
       "D                      0.301  0.046  0.061    0.1  0.093  0.071  0.082          \n",
       "E               0.695  0.129         0.045                                      \n",
       "F   0.71                                                                        \n",
       "G                      0.281  0.164  0.208  0.111  0.172  0.231   0.22  0.216   \n",
       "H                                                                               \n",
       "I         0.25                 0.05  0.047  0.055  0.036                        \n",
       "K               0.305         0.035                                             \n",
       "L   0.29                      0.089  0.077  0.038  0.041          0.07          \n",
       "M                                                                               \n",
       "N                                                                               \n",
       "P                             0.089   0.06  0.043         0.056         0.126   \n",
       "Q                             0.043                                             \n",
       "R                             0.164  0.074  0.068  0.058         0.046          \n",
       "S                      0.096  0.066  0.107   0.14  0.173  0.224  0.202  0.116   \n",
       "T         0.75                0.041  0.063  0.064  0.065  0.109  0.059          \n",
       "V                      0.092  0.104  0.073   0.13  0.095  0.085    0.1          \n",
       "W                                                                               \n",
       "Y                             0.058  0.139  0.173  0.154  0.143  0.118  0.542   \n",
       "\n",
       "     112A   112B    113    114    115    117    123  \n",
       "aa                                                   \n",
       "A          0.075          0.19                       \n",
       "C          0.054                                     \n",
       "D          0.087  0.135                              \n",
       "E                                                    \n",
       "F                               0.667                \n",
       "G    0.17  0.186   0.24  0.201                       \n",
       "H                                                    \n",
       "I                                       0.15         \n",
       "K                                                    \n",
       "L                                             0.672  \n",
       "M                               0.333                \n",
       "N                 0.105                              \n",
       "P                                       0.18         \n",
       "Q                                                    \n",
       "R                                                    \n",
       "S   0.191  0.169                                     \n",
       "T   0.131  0.111                              0.328  \n",
       "V          0.075                       0.348         \n",
       "W                        0.174                       \n",
       "Y   0.508  0.243   0.52  0.435         0.322         "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "\n",
    "aaColTemp=['A', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'K', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'V', 'W', 'Y']\n",
    "filecsv = r'D:/1A/Synthetic_library_design/GermlineGrouping20190919/test/HV1-69.fullLength.17aa.csv'\n",
    "myt = pd.read_csv(filecsv,index_col=0).T\n",
    "\n",
    "mytable = myt.replace(0,'')\n",
    "aaDict = {'aa':aaColTemp,'x':aaColTemp}\n",
    "df = pd.DataFrame(aaDict).set_index('aa')\n",
    "\n",
    "mydata=df.join(mytable).drop('x',axis=1)\n",
    "\n",
    "tab2w=mydata[mydata!=1].dropna(axis=1,how='any')\n",
    "tab2w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>pos</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>119</th>\n",
       "      <th>120</th>\n",
       "      <th>121</th>\n",
       "      <th>122</th>\n",
       "      <th>123</th>\n",
       "      <th>124</th>\n",
       "      <th>125</th>\n",
       "      <th>126</th>\n",
       "      <th>127</th>\n",
       "      <th>128</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>L</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.672</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.328</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 125 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "pos    1    2    3    4    5    6    7    8    9   10 ...   119  120  121  \\\n",
       "A    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0  0.0 ...   0.0  0.0  0.0   \n",
       "C    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "D    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "E    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0 ...   0.0  0.0  0.0   \n",
       "F    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "G    0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0  0.0  0.0 ...   1.0  0.0  1.0   \n",
       "H    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "I    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "K    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "L    0.0  0.0  0.0  1.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "M    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "N    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "P    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "Q    1.0  0.0  1.0  0.0  0.0  1.0  0.0  0.0  0.0  0.0 ...   0.0  1.0  0.0   \n",
       "R    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "S    0.0  0.0  0.0  0.0  0.0  0.0  1.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "T    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "V    0.0  1.0  0.0  0.0  1.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "W    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "Y    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0 ...   0.0  0.0  0.0   \n",
       "\n",
       "pos  122    123  124  125  126  127  128  \n",
       "A    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "C    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "D    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "E    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "F    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "G    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "H    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "I    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "K    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "L    0.0  0.672  0.0  0.0  0.0  0.0  0.0  \n",
       "M    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "N    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "P    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "Q    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "R    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "S    0.0  0.000  0.0  0.0  0.0  1.0  1.0  \n",
       "T    1.0  0.328  0.0  1.0  0.0  0.0  0.0  \n",
       "V    0.0  0.000  1.0  0.0  1.0  0.0  0.0  \n",
       "W    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "Y    0.0  0.000  0.0  0.0  0.0  0.0  0.0  \n",
       "\n",
       "[20 rows x 125 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Q',\n",
       " 'V',\n",
       " 'Q',\n",
       " 'L',\n",
       " 'V',\n",
       " 'Q',\n",
       " 'S',\n",
       " 'G',\n",
       " 'A',\n",
       " 'E',\n",
       " 'E',\n",
       " 'V',\n",
       " 'K',\n",
       " 'K',\n",
       " 'P',\n",
       " 'G',\n",
       " 'S',\n",
       " 'S',\n",
       " 'V',\n",
       " 'K',\n",
       " 'V',\n",
       " 'S',\n",
       " 'C',\n",
       " 'K',\n",
       " 'A',\n",
       " 'S',\n",
       " 'G',\n",
       " 'G',\n",
       " 'T',\n",
       " 'F',\n",
       " 'S',\n",
       " 'S',\n",
       " 'Y',\n",
       " 'A',\n",
       " 'I',\n",
       " 'S',\n",
       " 'W',\n",
       " 'V',\n",
       " 'R',\n",
       " 'Q',\n",
       " 'A',\n",
       " 'P',\n",
       " 'G',\n",
       " 'Q',\n",
       " 'G',\n",
       " 'L',\n",
       " 'E',\n",
       " 'W',\n",
       " 'M',\n",
       " 'G',\n",
       " 'G',\n",
       " 'I',\n",
       " 'I',\n",
       " 'P',\n",
       " 'I',\n",
       " 'F',\n",
       " 'G',\n",
       " 'T',\n",
       " 'A',\n",
       " 'N',\n",
       " 'Y',\n",
       " 'A',\n",
       " 'Q',\n",
       " 'K',\n",
       " 'F',\n",
       " 'Q',\n",
       " 'G',\n",
       " 'R',\n",
       " 'V',\n",
       " 'T',\n",
       " 'I',\n",
       " 'T',\n",
       " 'A',\n",
       " 'D',\n",
       " 'E',\n",
       " 'S',\n",
       " 'T',\n",
       " 'S',\n",
       " 'T',\n",
       " 'A',\n",
       " 'Y',\n",
       " 'M',\n",
       " 'E',\n",
       " 'L',\n",
       " 'S',\n",
       " 'S',\n",
       " 'L',\n",
       " 'R',\n",
       " 'S',\n",
       " 'E',\n",
       " 'D',\n",
       " 'T',\n",
       " 'A',\n",
       " 'V',\n",
       " 'Y',\n",
       " 'Y',\n",
       " 'C',\n",
       " 'A',\n",
       " 'R',\n",
       " 'D',\n",
       " 'G',\n",
       " 'G',\n",
       " 'Y',\n",
       " 'S',\n",
       " 'G',\n",
       " 'G',\n",
       " 'Y',\n",
       " 'Y',\n",
       " 'Y',\n",
       " 'Y',\n",
       " 'Y',\n",
       " 'F',\n",
       " 'D',\n",
       " 'V',\n",
       " 'W',\n",
       " 'G',\n",
       " 'Q',\n",
       " 'G',\n",
       " 'T',\n",
       " 'L',\n",
       " 'V',\n",
       " 'T',\n",
       " 'V',\n",
       " 'S',\n",
       " 'S']"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seqlist = myt.idxmax().tolist()\n",
    "seqlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'QVQLVQSGAEEVKKPGSSVKVSCKASGGTFSSYAISWVRQAPGQGLEWMGGIIPIFGTANYAQKFQGRVTITADESTSTAYMELSSLRSEDTAVYYCARDGGYSGGYYYYYFDVWGQGTLVTVSS'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seq = ''.join(seqlist)\n",
    "seq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "# app = xw.App(visible=False, add_book=False)\n",
    "# wb = app.books.open(fileout)\n",
    "\n",
    "\n",
    "# app.display_alerts = False\n",
    "# app.screen_updating = False\n",
    "\n",
    "path = r'D:/1A/Synthetic_library_design/GermlineGrouping20190919/test/'\n",
    "fileout = f'{path}/writeTest.xlsx'\n",
    "temp = f'{path}/SubmissionForm_Library_CVL_17Jul2019_v3.6.xlsx*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"'cp' 不是内部或外部命令，也不是可运行的程序\\n或批处理文件。\""
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import subprocess\n",
    "subprocess.getoutput(f'cp {temp} {fileout}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Sheet [writeTest.xlsx]CVL Input>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb = xw.Book(fileout)\n",
    "sht = wb.sheets['CVL Input']\n",
    "sht"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "sht.range('D13').value = '#'\n",
    "sht.range('D39').value = seq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['62',\n",
       " '64',\n",
       " '82',\n",
       " '107',\n",
       " '108',\n",
       " '109',\n",
       " '110',\n",
       " '111',\n",
       " '111A',\n",
       " '111B',\n",
       " '112',\n",
       " '112A',\n",
       " '112B',\n",
       " '113',\n",
       " '114',\n",
       " '115',\n",
       " '117',\n",
       " '123']"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tab2w.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "sht.range('H10').value = tab2w.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['',\n",
       "  '',\n",
       "  '',\n",
       "  0.1,\n",
       "  0.053,\n",
       "  0.046,\n",
       "  0.078,\n",
       "  0.113,\n",
       "  0.081,\n",
       "  0.10400000000000001,\n",
       "  '',\n",
       "  '',\n",
       "  0.075,\n",
       "  '',\n",
       "  0.19,\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.054000000000000006,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  0.301,\n",
       "  0.046,\n",
       "  0.061,\n",
       "  0.1,\n",
       "  0.09300000000000001,\n",
       "  0.071,\n",
       "  0.08199999999999999,\n",
       "  '',\n",
       "  '',\n",
       "  0.087,\n",
       "  0.135,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['',\n",
       "  '',\n",
       "  0.695,\n",
       "  0.129,\n",
       "  '',\n",
       "  0.045,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " [0.71, '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0.667, '', ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  0.281,\n",
       "  0.16399999999999998,\n",
       "  0.20800000000000002,\n",
       "  0.111,\n",
       "  0.172,\n",
       "  0.231,\n",
       "  0.22,\n",
       "  0.21600000000000003,\n",
       "  0.17,\n",
       "  0.18600000000000003,\n",
       "  0.24,\n",
       "  0.201,\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''],\n",
       " ['',\n",
       "  0.25,\n",
       "  '',\n",
       "  '',\n",
       "  0.05,\n",
       "  0.047,\n",
       "  0.055,\n",
       "  0.036000000000000004,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.15,\n",
       "  ''],\n",
       " ['',\n",
       "  '',\n",
       "  0.305,\n",
       "  '',\n",
       "  0.035,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " [0.29,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.08900000000000001,\n",
       "  0.077,\n",
       "  0.038,\n",
       "  0.040999999999999995,\n",
       "  '',\n",
       "  0.07,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.672],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.33299999999999996,\n",
       "  '',\n",
       "  ''],\n",
       " ['', '', '', '', '', '', '', '', '', '', '', '', '', 0.105, '', '', '', ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.08900000000000001,\n",
       "  0.06,\n",
       "  0.043,\n",
       "  '',\n",
       "  0.055999999999999994,\n",
       "  '',\n",
       "  0.126,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.18,\n",
       "  ''],\n",
       " ['', '', '', '', 0.043, '', '', '', '', '', '', '', '', '', '', '', '', ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.16399999999999998,\n",
       "  0.07400000000000001,\n",
       "  0.068,\n",
       "  0.057999999999999996,\n",
       "  '',\n",
       "  0.046,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  0.096,\n",
       "  0.066,\n",
       "  0.107,\n",
       "  0.14,\n",
       "  0.17300000000000001,\n",
       "  0.22399999999999998,\n",
       "  0.20199999999999999,\n",
       "  0.11599999999999999,\n",
       "  0.191,\n",
       "  0.16899999999999998,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['',\n",
       "  0.75,\n",
       "  '',\n",
       "  '',\n",
       "  0.040999999999999995,\n",
       "  0.063,\n",
       "  0.064,\n",
       "  0.065,\n",
       "  0.109,\n",
       "  0.059000000000000004,\n",
       "  '',\n",
       "  0.131,\n",
       "  0.111,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.32799999999999996],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  0.092,\n",
       "  0.10400000000000001,\n",
       "  0.073,\n",
       "  0.13,\n",
       "  0.095,\n",
       "  0.085,\n",
       "  0.1,\n",
       "  '',\n",
       "  '',\n",
       "  0.075,\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.348,\n",
       "  ''],\n",
       " ['', '', '', '', '', '', '', '', '', '', '', '', '', '', 0.174, '', '', ''],\n",
       " ['',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  0.057999999999999996,\n",
       "  0.139,\n",
       "  0.17300000000000001,\n",
       "  0.154,\n",
       "  0.14300000000000002,\n",
       "  0.11800000000000001,\n",
       "  0.542,\n",
       "  0.508,\n",
       "  0.243,\n",
       "  0.52,\n",
       "  0.435,\n",
       "  '',\n",
       "  0.322,\n",
       "  '']]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#https://codeday.me/bug/20180823/227073.html\n",
    "tab2w.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sht.range('H13').value = tab2w.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    0.053\n",
       "C         \n",
       "D    0.046\n",
       "E         \n",
       "F         \n",
       "G    0.164\n",
       "H         \n",
       "I     0.05\n",
       "K    0.035\n",
       "L    0.089\n",
       "M         \n",
       "N         \n",
       "P    0.089\n",
       "Q    0.043\n",
       "R    0.164\n",
       "S    0.066\n",
       "T    0.041\n",
       "V    0.104\n",
       "W         \n",
       "Y    0.058\n",
       "Name: 108, dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mytable['108']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Customer Defined Variants (Attach Variant List)'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sht.range('C13').value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Library Type\n",
      "#  Select - Library Type\n",
      "Uniform Variants (AA's)\n",
      "None\n",
      "Uniform Variants (Codons)\n",
      "None\n",
      "Precision Defined  Complexity Variant Regions\n",
      "None\n",
      "Customer Defined Variants (Attach Variant List)\n",
      "#\n",
      "AA Ratios\n",
      "None\n",
      "Wt Ratios\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "for i in  sht['C9:D15']:\n",
    "    print(i.value)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>56</th>\n",
       "      <th>57</th>\n",
       "      <th>58</th>\n",
       "      <th>59</th>\n",
       "      <th>62</th>\n",
       "      <th>63</th>\n",
       "      <th>64</th>\n",
       "      <th>65</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>0.822</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>L</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V</th>\n",
       "      <td>0.178</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       56 57 58 59 62 63 64 65\n",
       "aa                            \n",
       "A                             \n",
       "C                             \n",
       "D          1        1         \n",
       "E                1        1   \n",
       "F   0.822                     \n",
       "G                      1      \n",
       "H                             \n",
       "I                             \n",
       "K                             \n",
       "L                             \n",
       "M                             \n",
       "N                             \n",
       "P             1               \n",
       "Q                             \n",
       "R                             \n",
       "S                             \n",
       "T                            1\n",
       "V   0.178                     \n",
       "W                             \n",
       "Y                             "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "\n",
    "aaColTemp=['A', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'K', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'V', 'W', 'Y']\n",
    "filecsv = r'D:/1A/Synthetic_library_design/GermlineGrouping20190919/test/test.cdrh2.csv'\n",
    "mytable = pd.read_csv(filecsv,index_col=0).replace(0,'').T\n",
    "\n",
    "aaDict = {'aa':aaColTemp,'x':aaColTemp}\n",
    "df = pd.DataFrame(aaDict).set_index('aa')\n",
    "\n",
    "tab2w=df.join(mytable).drop('x',axis=1)\n",
    "tab2w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>119</th>\n",
       "      <th>120</th>\n",
       "      <th>121</th>\n",
       "      <th>122</th>\n",
       "      <th>123</th>\n",
       "      <th>124</th>\n",
       "      <th>125</th>\n",
       "      <th>126</th>\n",
       "      <th>127</th>\n",
       "      <th>128</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>L</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.672</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.328</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V</th>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 125 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      1    2    3    4    5    6    7    8    9   10 ...   119  120  121  122  \\\n",
       "aa                                                   ...                        \n",
       "A                                           NaN      ...                        \n",
       "C                                                    ...                        \n",
       "D                                                    ...                        \n",
       "E                                                NaN ...                        \n",
       "F                                                    ...                        \n",
       "G                                      NaN           ...   NaN       NaN        \n",
       "H                                                    ...                        \n",
       "I                                                    ...                        \n",
       "K                                                    ...                        \n",
       "L                  NaN                               ...                        \n",
       "M                                                    ...                        \n",
       "N                                                    ...                        \n",
       "P                                                    ...                        \n",
       "Q   NaN       NaN            NaN                     ...        NaN             \n",
       "R                                                    ...                        \n",
       "S                                 NaN                ...                        \n",
       "T                                                    ...                  NaN   \n",
       "V        NaN            NaN                          ...                        \n",
       "W                                                    ...                        \n",
       "Y                                                    ...                        \n",
       "\n",
       "      123  124  125  126  127  128  \n",
       "aa                                  \n",
       "A                                   \n",
       "C                                   \n",
       "D                                   \n",
       "E                                   \n",
       "F                                   \n",
       "G                                   \n",
       "H                                   \n",
       "I                                   \n",
       "K                                   \n",
       "L   0.672                           \n",
       "M                                   \n",
       "N                                   \n",
       "P                                   \n",
       "Q                                   \n",
       "R                                   \n",
       "S                         NaN  NaN  \n",
       "T   0.328       NaN                 \n",
       "V          NaN       NaN            \n",
       "W                                   \n",
       "Y                                   \n",
       "\n",
       "[20 rows x 125 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t= tab2w[tab2w!=1]\n",
    "t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>62</th>\n",
       "      <th>64</th>\n",
       "      <th>82</th>\n",
       "      <th>107</th>\n",
       "      <th>108</th>\n",
       "      <th>109</th>\n",
       "      <th>110</th>\n",
       "      <th>111</th>\n",
       "      <th>111A</th>\n",
       "      <th>111B</th>\n",
       "      <th>112</th>\n",
       "      <th>112A</th>\n",
       "      <th>112B</th>\n",
       "      <th>113</th>\n",
       "      <th>114</th>\n",
       "      <th>115</th>\n",
       "      <th>117</th>\n",
       "      <th>123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.053</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.078</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.081</td>\n",
       "      <td>0.104</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.075</td>\n",
       "      <td></td>\n",
       "      <td>0.19</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.301</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.061</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.071</td>\n",
       "      <td>0.082</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.087</td>\n",
       "      <td>0.135</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.695</td>\n",
       "      <td>0.129</td>\n",
       "      <td></td>\n",
       "      <td>0.045</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>0.71</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.667</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.281</td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.208</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.172</td>\n",
       "      <td>0.231</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.216</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.186</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.201</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td></td>\n",
       "      <td>0.25</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.047</td>\n",
       "      <td>0.055</td>\n",
       "      <td>0.036</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.15</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.305</td>\n",
       "      <td></td>\n",
       "      <td>0.035</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>L</th>\n",
       "      <td>0.29</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.089</td>\n",
       "      <td>0.077</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.041</td>\n",
       "      <td></td>\n",
       "      <td>0.07</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.333</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.105</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.089</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.043</td>\n",
       "      <td></td>\n",
       "      <td>0.056</td>\n",
       "      <td></td>\n",
       "      <td>0.126</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.18</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.043</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.068</td>\n",
       "      <td>0.058</td>\n",
       "      <td></td>\n",
       "      <td>0.046</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.096</td>\n",
       "      <td>0.066</td>\n",
       "      <td>0.107</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.224</td>\n",
       "      <td>0.202</td>\n",
       "      <td>0.116</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.169</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td></td>\n",
       "      <td>0.75</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.041</td>\n",
       "      <td>0.063</td>\n",
       "      <td>0.064</td>\n",
       "      <td>0.065</td>\n",
       "      <td>0.109</td>\n",
       "      <td>0.059</td>\n",
       "      <td></td>\n",
       "      <td>0.131</td>\n",
       "      <td>0.111</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.092</td>\n",
       "      <td>0.104</td>\n",
       "      <td>0.073</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.085</td>\n",
       "      <td>0.1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.075</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.348</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.174</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.139</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.154</td>\n",
       "      <td>0.143</td>\n",
       "      <td>0.118</td>\n",
       "      <td>0.542</td>\n",
       "      <td>0.508</td>\n",
       "      <td>0.243</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.435</td>\n",
       "      <td></td>\n",
       "      <td>0.322</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      62    64     82    107    108    109    110    111   111A   111B    112  \\\n",
       "aa                                                                              \n",
       "A                        0.1  0.053  0.046  0.078  0.113  0.081  0.104          \n",
       "C                                                                               \n",
       "D                      0.301  0.046  0.061    0.1  0.093  0.071  0.082          \n",
       "E               0.695  0.129         0.045                                      \n",
       "F   0.71                                                                        \n",
       "G                      0.281  0.164  0.208  0.111  0.172  0.231   0.22  0.216   \n",
       "H                                                                               \n",
       "I         0.25                 0.05  0.047  0.055  0.036                        \n",
       "K               0.305         0.035                                             \n",
       "L   0.29                      0.089  0.077  0.038  0.041          0.07          \n",
       "M                                                                               \n",
       "N                                                                               \n",
       "P                             0.089   0.06  0.043         0.056         0.126   \n",
       "Q                             0.043                                             \n",
       "R                             0.164  0.074  0.068  0.058         0.046          \n",
       "S                      0.096  0.066  0.107   0.14  0.173  0.224  0.202  0.116   \n",
       "T         0.75                0.041  0.063  0.064  0.065  0.109  0.059          \n",
       "V                      0.092  0.104  0.073   0.13  0.095  0.085    0.1          \n",
       "W                                                                               \n",
       "Y                             0.058  0.139  0.173  0.154  0.143  0.118  0.542   \n",
       "\n",
       "     112A   112B    113    114    115    117    123  \n",
       "aa                                                   \n",
       "A          0.075          0.19                       \n",
       "C          0.054                                     \n",
       "D          0.087  0.135                              \n",
       "E                                                    \n",
       "F                               0.667                \n",
       "G    0.17  0.186   0.24  0.201                       \n",
       "H                                                    \n",
       "I                                       0.15         \n",
       "K                                                    \n",
       "L                                             0.672  \n",
       "M                               0.333                \n",
       "N                 0.105                              \n",
       "P                                       0.18         \n",
       "Q                                                    \n",
       "R                                                    \n",
       "S   0.191  0.169                                     \n",
       "T   0.131  0.111                              0.328  \n",
       "V          0.075                       0.348         \n",
       "W                        0.174                       \n",
       "Y   0.508  0.243   0.52  0.435         0.322         "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t.dropna(axis=1,how='any')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>62</th>\n",
       "      <th>64</th>\n",
       "      <th>82</th>\n",
       "      <th>107</th>\n",
       "      <th>108</th>\n",
       "      <th>109</th>\n",
       "      <th>110</th>\n",
       "      <th>111</th>\n",
       "      <th>111A</th>\n",
       "      <th>111B</th>\n",
       "      <th>112</th>\n",
       "      <th>112A</th>\n",
       "      <th>112B</th>\n",
       "      <th>113</th>\n",
       "      <th>114</th>\n",
       "      <th>115</th>\n",
       "      <th>117</th>\n",
       "      <th>123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.053</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.078</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.081</td>\n",
       "      <td>0.104</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.075</td>\n",
       "      <td></td>\n",
       "      <td>0.19</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.301</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.061</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.071</td>\n",
       "      <td>0.082</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.087</td>\n",
       "      <td>0.135</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.695</td>\n",
       "      <td>0.129</td>\n",
       "      <td></td>\n",
       "      <td>0.045</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>0.71</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.667</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.281</td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.208</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.172</td>\n",
       "      <td>0.231</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.216</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.186</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.201</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td></td>\n",
       "      <td>0.25</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.047</td>\n",
       "      <td>0.055</td>\n",
       "      <td>0.036</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.15</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.305</td>\n",
       "      <td></td>\n",
       "      <td>0.035</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>L</th>\n",
       "      <td>0.29</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.089</td>\n",
       "      <td>0.077</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.041</td>\n",
       "      <td></td>\n",
       "      <td>0.07</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.333</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.105</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.089</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.043</td>\n",
       "      <td></td>\n",
       "      <td>0.056</td>\n",
       "      <td></td>\n",
       "      <td>0.126</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.18</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.043</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.068</td>\n",
       "      <td>0.058</td>\n",
       "      <td></td>\n",
       "      <td>0.046</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.096</td>\n",
       "      <td>0.066</td>\n",
       "      <td>0.107</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.224</td>\n",
       "      <td>0.202</td>\n",
       "      <td>0.116</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.169</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T</th>\n",
       "      <td></td>\n",
       "      <td>0.75</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.041</td>\n",
       "      <td>0.063</td>\n",
       "      <td>0.064</td>\n",
       "      <td>0.065</td>\n",
       "      <td>0.109</td>\n",
       "      <td>0.059</td>\n",
       "      <td></td>\n",
       "      <td>0.131</td>\n",
       "      <td>0.111</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.092</td>\n",
       "      <td>0.104</td>\n",
       "      <td>0.073</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.085</td>\n",
       "      <td>0.1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.075</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.348</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.174</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.139</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.154</td>\n",
       "      <td>0.143</td>\n",
       "      <td>0.118</td>\n",
       "      <td>0.542</td>\n",
       "      <td>0.508</td>\n",
       "      <td>0.243</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.435</td>\n",
       "      <td></td>\n",
       "      <td>0.322</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      62    64     82    107    108    109    110    111   111A   111B    112  \\\n",
       "aa                                                                              \n",
       "A                        0.1  0.053  0.046  0.078  0.113  0.081  0.104          \n",
       "C                                                                               \n",
       "D                      0.301  0.046  0.061    0.1  0.093  0.071  0.082          \n",
       "E               0.695  0.129         0.045                                      \n",
       "F   0.71                                                                        \n",
       "G                      0.281  0.164  0.208  0.111  0.172  0.231   0.22  0.216   \n",
       "H                                                                               \n",
       "I         0.25                 0.05  0.047  0.055  0.036                        \n",
       "K               0.305         0.035                                             \n",
       "L   0.29                      0.089  0.077  0.038  0.041          0.07          \n",
       "M                                                                               \n",
       "N                                                                               \n",
       "P                             0.089   0.06  0.043         0.056         0.126   \n",
       "Q                             0.043                                             \n",
       "R                             0.164  0.074  0.068  0.058         0.046          \n",
       "S                      0.096  0.066  0.107   0.14  0.173  0.224  0.202  0.116   \n",
       "T         0.75                0.041  0.063  0.064  0.065  0.109  0.059          \n",
       "V                      0.092  0.104  0.073   0.13  0.095  0.085    0.1          \n",
       "W                                                                               \n",
       "Y                             0.058  0.139  0.173  0.154  0.143  0.118  0.542   \n",
       "\n",
       "     112A   112B    113    114    115    117    123  \n",
       "aa                                                   \n",
       "A          0.075          0.19                       \n",
       "C          0.054                                     \n",
       "D          0.087  0.135                              \n",
       "E                                                    \n",
       "F                               0.667                \n",
       "G    0.17  0.186   0.24  0.201                       \n",
       "H                                                    \n",
       "I                                       0.15         \n",
       "K                                                    \n",
       "L                                             0.672  \n",
       "M                               0.333                \n",
       "N                 0.105                              \n",
       "P                                       0.18         \n",
       "Q                                                    \n",
       "R                                                    \n",
       "S   0.191  0.169                                     \n",
       "T   0.131  0.111                              0.328  \n",
       "V          0.075                       0.348         \n",
       "W                        0.174                       \n",
       "Y   0.508  0.243   0.52  0.435         0.322         "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tab2w[tab2w!=1].dropna(axis=1,how='any')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>价格</th>\n",
       "      <th>数量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水果</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>苹果</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>梨</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>草莓</th>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    价格  数量\n",
       "水果        \n",
       "苹果   3   9\n",
       "梨    4   8\n",
       "草莓   5   7"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left1 = pd.DataFrame({'水果':['苹果','梨','草莓'],\n",
    "                  '价格':[3,4,5],\n",
    "                  '数量':[9,8,7]}).set_index('水果')\n",
    "right1 = pd.DataFrame({'水果':['苹果','草莓'],\n",
    "                   '产地':['美国','中国']})\n",
    "left1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>水果</th>\n",
       "      <th>产地</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>苹果</td>\n",
       "      <td>美国</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>草莓</td>\n",
       "      <td>中国</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   水果  产地\n",
       "0  苹果  美国\n",
       "1  草莓  中国"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>价格</th>\n",
       "      <th>数量</th>\n",
       "      <th>水果</th>\n",
       "      <th>产地</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>苹果</td>\n",
       "      <td>美国</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>梨</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>草莓</td>\n",
       "      <td>中国</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   价格  数量  水果   产地\n",
       "0   3   9  苹果   美国\n",
       "1   4   8   梨  NaN\n",
       "1   5   7  草莓   中国"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left1,right1,right_on='水果',left_index=True,how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "left2 = left1\n",
    "right2 = right1.set_index('水果')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>价格</th>\n",
       "      <th>数量</th>\n",
       "      <th>产地</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水果</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>苹果</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>美国</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>梨</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>草莓</th>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>中国</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    价格  数量   产地\n",
       "水果             \n",
       "苹果   3   9   美国\n",
       "梨    4   8  NaN\n",
       "草莓   5   7   中国"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left2.join(right2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
